/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package pe.edu.pucp.Reportes.DAO;

import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import pe.edu.pucp.Modelo.Aeropuerto;
import pe.edu.pucp.Modelo.Ciudad;
import pe.edu.pucp.Modelo.Continente;
import pe.edu.pucp.Modelo.Pais;
import pe.edu.pucp.Util.Constante;
import pe.edu.pucp.Util.HibernateUtil;
import pe.edu.pucp.Util.Reportclass.RepKardex;

/**
 *
 * @author jorge
 */
public class ReporteKardexDAO {
    
    public ArrayList<Continente> listarContinente(){
        ArrayList<Continente> listaContinente = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = session.beginTransaction();
                    
        String q = "select ct from Continente ct order by ct.idcontinente"; 
        
        Query query = session.createQuery(q);    
        List lista = query.list();
        Iterator iter = lista.iterator(); 
        while (iter.hasNext()) {    
            Continente a = (Continente) iter.next();
            listaContinente.add(a);
        }
        
        tx.commit();
        session.close();

        return listaContinente;
    }      
            
   
    public ArrayList<Pais> listarPais(int idcont){
        ArrayList<Pais> listaPais = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = session.beginTransaction();
        
        String q = "select p from Pais p where p.continente.idcontinente = :id order by p.idpais";       
        
        Query query = session.createQuery(q);       
        query.setInteger("id", idcont);
        List lista = query.list();
        Iterator iter = lista.iterator();        
        while (iter.hasNext()) {    
            Pais a = (Pais) iter.next();
            Hibernate.initialize(a.getContinente());
            listaPais.add(a);
        }
        
        tx.commit();
        session.close();

        return listaPais;
    }       
    
    public ArrayList<Ciudad> listarCiudad(int idpa){
        ArrayList<Ciudad> listaCiudad = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = session.beginTransaction();

         String q = "select c from Ciudad c where c.pais.idpais = :id order by c.idciudad";       
        
        Query query = session.createQuery(q);          
        query.setInteger("id", idpa);
        List lista = query.list();        
        Iterator iter = lista.iterator(); 
        while (iter.hasNext()) {    
            Ciudad a = (Ciudad) iter.next();
            Hibernate.initialize(a.getPais());
            Hibernate.initialize(a.getPais().getContinente());
            listaCiudad.add(a);
        }
        
        tx.commit();
        session.close();

        return listaCiudad;
    }    
    
    public ArrayList<Aeropuerto> listarAeropuerto(int idciu){
        ArrayList<Aeropuerto> listaAeropuerto = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = session.beginTransaction();
        
        String q = "select a from Aeropuerto a where a.ciudad.idciudad = :id order by a.idaeropuerto";
       
        Query query = session.createQuery(q);        
        query.setInteger("id", idciu);
        List lista = query.list();        
        Iterator iter = lista.iterator(); 
        while (iter.hasNext()) {    
            Aeropuerto a = (Aeropuerto) iter.next();
            Hibernate.initialize(a.getCiudad());
            Hibernate.initialize(a.getCiudad().getPais());
            Hibernate.initialize(a.getCiudad().getPais().getContinente());
            listaAeropuerto.add(a);
        }
        
        tx.commit();
        session.close();

        return listaAeropuerto;
    }       
    
    public int inicioKardex(int cont, int pais, int ciud, Date fecha){
        int suma=0;
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = session.beginTransaction();        
        String q = "select cast(sum(case when i.idtipomov = :ent then e.numpack when i.idtipomov = :sal then -e.numpack else 0 end) as int) sum1 "
                + "from Inventario i inner join Envio e on (i.idenvio = e.idenvio) "
                + "inner join Aeropuerto a on (i.idaeropuerto = a.idaeropuerto) "
                + "inner join Ciudad c on (a.idciudad = c.idciudad) "
                + "inner join Pais p on (c.idpais = p.idpais) "
                + "inner join Continente ct on (p.idcontinente = ct.idcontinente) "
                + "where cast(i.fechareg as date) < cast(:dini as date) ";        
        if (cont>0){ q+="where ct.idcontinente = :cont "; }
        if (pais>0){ q+="and p.idpais = :pais "; }
        if (ciud>0){ q+="and c.idciudad = :ciud "; }                                
        
        Query query = session.createSQLQuery(q);
        query.setInteger("ent", Constante.TIPO_MOVIMIENTO_ENTRADA);   
        query.setInteger("sal", Constante.TIPO_MOVIMIENTO_SALIDA);
        query.setDate("dini", fecha);
        if (cont>0){ query.setInteger("cont", cont); }                
        if (pais>0){ query.setInteger("pais", pais); }
        if (ciud>0){ query.setInteger("ciud", ciud); }
        
        List lista = query.list();        
        Iterator iter = lista.iterator(); 
        if (iter.hasNext()) {    
            Object[] row = (Object[]) iter.next();            
            if (row!=null){
                suma = (int)row[0];                
            }
        }

        tx.commit();
        session.close();
        
        return suma;
    }
    
    
    public ArrayList<RepKardex> listarKardex(int cont, int pais, int ciud, Date inicio, Date fin, int aper){        
        ArrayList<RepKardex> listaKardex = new ArrayList<>();
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = session.beginTransaction();        
        String q = "select cast(sum(case when i.idtipomov = :ent then e.numpack else 0 end) as int) as sum1, "
                + "cast(sum(case when i.idtipomov = :sal then e.numpack else 0 end) as int) as sum2, "
                + "cast(i.fechareg as date) "
                + "from Inventario i, Envio e, Aeropuerto a, Ciudad c, Pais p, Continente ct "
                + "where i.idenvio = e.idenvio and i.idaeropuerto = a.idaeropuerto "
                + "and a.idciudad = c.idciudad and c.idpais = p.idpais and p.idcontinente = ct.idcontinente ";
        if (cont>0){ q+="and ct.idcontinente = :cont "; }
        if (pais>0){ q+="and p.idpais = :pais "; }
        if (ciud>0){ q+="and c.idciudad = :ciud "; }                
        if (inicio!=null){ q+="and cast(i.fechareg as date) >= cast(:dini as date) "; }
        if (fin!=null){ q+="and cast(i.fechareg as date) <= cast(:dfin as date) "; }             
        q+= "group by cast(i.fechareg as date) "
                + "order by cast(i.fechareg as date)";        
       
        Query query = session.createSQLQuery(q);   
        query.setInteger("ent", Constante.TIPO_MOVIMIENTO_ENTRADA);
        query.setInteger("sal", Constante.TIPO_MOVIMIENTO_SALIDA);
        if (cont>0){ query.setInteger("cont", cont); }                
        if (pais>0){ query.setInteger("pais", pais); }
        if (ciud>0){ query.setInteger("ciud", ciud); }
        if (inicio!=null){ query.setDate("dini", inicio); }
        if (fin!=null){ query.setDate("dfin", fin); }           
        List lista = query.list();        
        Iterator iter = lista.iterator(); 
        while (iter.hasNext()) {    
            Object[] row = (Object[]) iter.next();            
            if (row!=null){                
                RepKardex rep = new RepKardex((int)row[0],(int)row[1],(Date)row[2],aper,aper+(int)row[0]-(int)row[1]);
                listaKardex.add(rep);
                aper = aper+(int)row[0]-(int)row[1];
            }

        }

        tx.commit();
        session.close();
        
        return listaKardex;
    }               
 
    public ArrayList<RepKardex> listarKardex(){        
        ArrayList<RepKardex> listaKardex = new ArrayList<>();
        int aper=0;
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction tx = session.beginTransaction();        
        String q = "select cast(sum(case when i.idtipomov = :ent then e.numpack else 0 end) as int) as sum1, "
                + "cast(sum(case when i.idtipomov = :sal then e.numpack else 0 end) as int) as sum2, "
                + "cast(i.fechareg as date) "
                + "from Inventario i, Envio e, Aeropuerto a, Ciudad c, Pais p, Continente ct "
                + "where i.idenvio = e.idenvio and i.idaeropuerto = a.idaeropuerto "
                + "and a.idciudad = c.idciudad and c.idpais = p.idpais and p.idcontinente = ct.idcontinente "
                + "group by cast(i.fechareg as date) "
                + "order by cast(i.fechareg as date)";        
       
        Query query = session.createSQLQuery(q);   
        query.setInteger("ent", Constante.TIPO_MOVIMIENTO_ENTRADA);
        query.setInteger("sal", Constante.TIPO_MOVIMIENTO_SALIDA);      
        List lista = query.list();        
        Iterator iter = lista.iterator(); 
        while (iter.hasNext()) {    
            Object[] row = (Object[]) iter.next();            
            if (row!=null){                
                RepKardex rep = new RepKardex((int)row[0],(int)row[1],(Date)row[2],aper,aper+(int)row[0]-(int)row[1]);
                listaKardex.add(rep);
                aper = aper+(int)row[0]-(int)row[1];
            }

        }

        tx.commit();
        session.close();
        
        return listaKardex;
    }     
    
}

